--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';
DROP  TABLE se_met_individu ;
CREATE TABLE se_met_individu AS (SELECT * FROM &erp.met_individu WHERE 1=2);


DELETE FROM se_met_individu;

 INSERT INTO se_met_individu
 (SELECT CLIENT client_cod,
         LONGU individu_cod,
         0 enreg_id,
         'N' enreg_s,
         NOM nom,
         SubStr(PRENOM,1,30) prenom,
         METIER metier_lib,
         REGROUP regroup1_lib,
         TAILLE regroup2_lib,
         MATRICULE matricule_lib
    FROM UV_CINDIVIDUS i
    WHERE  EXISTS (SELECT 1 FROM uv_cclients c WHERE c.id=i.client)) ;

 UPDATE se_met_individu tf SET enreg_id=(select enreg_id FROM &erp.met_individu f WHERE f.client_cod=tf.client_cod AND f.individu_cod=tf.individu_cod) ;
 UPDATE se_met_individu tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('client_cod@individu_cod','MET_INDIVIDU','O') ;

 commit;

select * from (
SELECT &outils.stragg(source) source,client_cod, individu_cod, enreg_id, enreg_s, nom, prenom, metier_lib, regroup1_lib, regroup2_lib, matricule_lib
FROM (
  SELECT 'uv' source ,g.* FROM se_met_individu g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.met_individu g
  )
GROUP BY client_cod, individu_cod, enreg_id, enreg_s, nom, prenom, metier_lib, regroup1_lib, regroup2_lib, matricule_lib
HAVING Count(1)!=2) r
ORDER BY client_cod,individu_cod